﻿
CREATE PROCEDURE [dbo].[report_notpayed_input_invoices]
	(	
	@p_nsi_organization_id	int,
	@p_contract_id	int,
	@p_contract_number	nvarchar(255),
	@p_folder_num int,
	@p_nsi_type_of_activity_id	int,
	@p_plan_payment_date date,
	@p_nsi_pay_direct_id int,
	@p_nsi_fin_res_id int,
	@p_nsi_expense_item_id int
	)
AS
	SET NOCOUNT ON
	
	SELECT ii.ID, ii.NUMBER, ii.REG_NUMBER, ii.[DATE], ii.CONTRACT_NUMBER, ii.CONTRACT_DATE, [dbo].[f_convert_to_rur](ii.SUM, GETDATE(), ii.NSI_CURRENCY_ID) as SUM_RUR, ii.[SUM] as SUM_CUR, c.CODE as CUR_CODE, c.SHORT_NAME as CUR_NAME,
		o.SHORT_NAME as ORGANIZATION_NAME, o.COUNTRY as ORGANIZATION_COUNTRY, [dbo].[f_invoice_in_get_rest](ii.ID) as REST_SUM_RUR, oo.SHORT_NAME as CUSTOMER, cn.NUMBER as CONTRACT_NUM_CUSTOMER
	FROM INVOICE_IN as ii
	LEFT JOIN NSI_ORGANIZATION as o
		ON o.id = ii.NSI_ORGANIZATION_ID	
	LEFT JOIN NSI_CURRENCY as c
		ON c.ID = ii.NSI_CURRENCY_ID
	LEFT JOIN [CONTRACT] as cn 
		ON cn.ID = ii.NSI_CONTRACT_ID
	LEFT JOIN NSI_ORGANIZATION as oo
		ON oo.ID = cn.NSI_ORGANIZATION_ID
	LEFT JOIN NSI_FINANCE_RESOURCE as fi
		ON ii.NSI_FINANCE_RESOURCE_ID = fi.ID
	LEFT JOIN NSI_EXPENSE_ITEM as ei
		ON ii.NSI_EXPENSE_ITEM_ID = ei.ID
	WHERE	
		(
			ii.STATE = 'Оплачен частично'	
			OR
			ii.STATE = 'Не оплачен'
			OR
			ii.STATE IS NULL
		)	
		AND
		(
			(@p_nsi_organization_id is null)
			OR
			(ii.NSI_ORGANIZATION_ID = @p_nsi_organization_id)			
		)
		AND
		(
			(@p_contract_id is null)
			OR
			(ii.NSI_CONTRACT_ID = @p_contract_id)
		)
		AND
		(
			(@p_folder_num is null)
			OR
			(ii.N_FOLDER = @p_folder_num)
		)
		AND
		(
			(@p_contract_number is null)
			OR
			(ii.CONTRACT_NUMBER = @p_contract_number)
		)
		AND
		(
			(@p_nsi_type_of_activity_id is null)
			OR
			(ii.NSI_TYPE_OF_ACTIVITY_ID = @p_nsi_type_of_activity_id)
		)
		AND
		(
			(@p_plan_payment_date is null)
			OR
			(ii.PLAN_PAY_DATE LIKE @p_plan_payment_date)
		)
		AND
		(
			(@p_nsi_pay_direct_id is null)
			OR
			(ii.NSI_PAY_DIRECT_ID = @p_nsi_pay_direct_id)
		)
		AND
		(
			(@p_nsi_fin_res_id is null)
			OR
			(fi.id = @p_nsi_fin_res_id)
		)
		AND
		(
			(@p_nsi_expense_item_id is null)
			OR
			(ei.id = @p_nsi_expense_item_id)
		)
		ORDER BY ii.REG_NUMBER